<?php

/**
 * @author Laurent SITTLER <lolo.sittler@gmail.com>
 * @author Alexandre GOMEZ <gomez.alexandre02@gmail.com>
 * @version 0.1
 */
require_once 'Library/DB.php';
require_once 'Library/Type.php';
require_once 'Library/Rule.php';

/**
 * Database Model Class
 */
class Database_Model extends Model {

    /**
     * Build Database Modl
     */
    function __construct() {
        parent::__construct();
    }

    /**
     * Show Database of Current User Grouped by Rule
     * @return Array<Array<Key, Value>>
     */
    public function GetDB($id) {
        $repGroup = $this->db->prepare("SELECT * FROM RFA_SGBD_RULE ORDER BY RULE_ID");
        $repGroup->setFetchMode(PDO::FETCH_ASSOC);
        $repGroup->execute();

        while ($resultGroup = $repGroup->fetch()) {
            $repCount = $this->db->prepare("SELECT count(*) FROM RFU_SGBD_DATABASE WHERE DB_USER_ID LIKE :id AND DB_RULE_ID LIKE :rule");
            $repCount->execute(array(
                ":id" => $id,
                ":rule" => $resultGroup['RULE_ID']
            ));
            $count = $repCount->fetchAll();
            if ($count[0][0] > 0){
                echo '<h4>' . $resultGroup['RULE_NM'] . ' (' . $count[0][0] . ')</h4>';
                $repDB = $this->db->prepare("SELECT * FROM RFU_SGBD_DATABASE WHERE DB_USER_ID LIKE :id AND DB_RULE_ID LIKE :rule ORDER BY DB_ID");
                $repDB->setFetchMode(PDO::FETCH_ASSOC);
                $repDB->execute(array(
                    ":id" => $id,
                    ":rule" => $resultGroup['RULE_ID']
                ));
                while ($resultDB = $repDB->fetch()) {
                    echo '<button value="' . $resultDB['DB_ID'] . '" class="bddLink">' . $resultDB['DB_NNM'] . '</button>';
                }
                $repDB->closeCursor();
            }
            $repCount->closeCursor();
        }
        $repGroup->closeCursor();
    }

    /**
     * Get all database by User
     * @param Integer $id
     * @return array<DB>
     */
    public function GetDBByUser($id) {
        $list = array();
        $rep = $this->db->prepare("SELECT * FROM RFU_SGBD_DATABASE WHERE DB_USER_ID LIKE :id ORDER BY DB_ID");
        $rep->execute(array(
            ":id" => $id
        ));
        //print_r($rep);
        while ($result = $rep->fetch()) {
            $bdd = new DB();
            $bdd->SetDB($result['DB_ID'], $result['DB_NNM'], $result['DB_HOST'], $result['DB_PORT'], $result['DB_NM'], $result['DB_TDT_ID'], $result['DB_USER_ID'], $result['DB_RULE_ID']);
            array_push($list, $bdd);
        }
        $rep->closeCursor();
        return $list;
    }

    /**
     * Add a database
     * @param String $name
     * @param Integer $dbtype
     * @param String $dbhost
     * @param Integer $dbport
     * @param String $dbname
     * @param Integer $userid
     * @param Integer $ruleid
     * @return Last ID insert
     */
    public function AddDB($name, $dbtype, $dbhost, $dbport, $dbname, $userid, $ruleid) {
        $clog = $this->db->prepare("SELECT rfu_sgbd_database_seq.NEXTVAL FROM dual");
        $clog->setFetchMode(PDO::FETCH_ASSOC);
        $clog->execute();
        $lastInsert = $clog->fetchAll();
        $clog->closeCursor();
        
        $req = $this->db->prepare("INSERT INTO RFU_SGBD_DATABASE (DB_ID, DB_NNM, DB_HOST, DB_PORT, DB_NM, DB_CREATED, DB_TDT_ID, DB_USER_ID, DB_RULE_ID) VALUES (:id, :nickname, :host, :port, :name, to_date(:dcreate, 'DD-MM-YYYY HH24:MI'), :tdtId, :userId, :ruleId)");
        $req->execute(array(
            ":id" => 0,
            ":nickname" => $name,
            ":host" => $dbhost,
            ":port" => $dbport,
            ":name" => $dbname,
            ":dcreate" => date('d-m-Y H:m'),
            ":tdtId" => $dbtype,
            ":userId" => $userid,
            ":ruleId" => $ruleid
        ));
        $req->closeCursor();
         return $lastInsert[0]['NEXTVAL'] + 1;
    }

    /**
     * Remove a database
     * @param Integer $id
     */
    public function DeleteDB($id) {
        $req = $this->db->prepare("DELETE FROM RFU_SGBD_DATABASE WHERE DB_ID LIKE :id");
        $req->execute(array(
            ":id" => $id
        ));
        $req->closeCursor();
    }
    
    /**
     * Delete all user's databases
     * @param Integer $id
     */
    public function DeleteAllDBUser($id){
        $req = $this->db->prepare("DELETE FROM RFU_SGBD_DATABASE WHERE DB_USER_ID LIKE :id");
        $req->execute(array(
            ":id" => $id
        ));
        $req->closeCursor();
    }

    /**
     * Get Type by ID
     * @param Integer $id
     * @return Type
     */
    public function GetTypeByID($id) {
        $type = new Type();
        $req = $this->db->prepare("SELECT * FROM RFA_SGBD_TYPE_DATABASE WHERE TDT_ID = :id");
        $req->execute(array(
            ":id" => $id
        ));
        $res = $req->fetchAll();
        $type->SetType($res[0]['TDT_ID'], $res[0]['TDT_NM']);
        $req->closeCursor();
        return $type;
    }

    /**
     * Get Rule by ID
     * @param Integer $id
     * @return Rule
     */
    public function GetRuleByID($id) {
        $rule = new Rule();
        $req = $this->db->prepare("SELECT * FROM RFA_SGBD_RULE WHERE RULE_ID = :id");
        $req->execute(array(
            ":id" => $id
        ));
        $res = $req->fetchAll();
        $rule->SetRule($res[0]['RULE_ID'], $res[0]['RULE_NM']);
        $req->closeCursor();
        return $rule;
    }

    /**
     * Get all Rule
     * @return array<Rule>
     */
    public function GetAllRule() {
        $list = array();
        $req = $this->db->prepare("SELECT * FROM RFA_SGBD_RULE ORDER BY RULE_ID");
        $req->execute();
        while ($result = $req->fetch()) {
            $u = new Rule();
            $u->SetRule($result['RULE_ID'], $result['RULE_NM']);
            array_push($list, $u);
        }
        $req->closeCursor();
        return $list;
    }

    /**
     * Get all Type
     * @return array<Type>
     */
    public function GetAllType() {
        $list = array();
        $req = $this->db->prepare("SELECT * FROM RFA_SGBD_TYPE_DATABASE ORDER BY TDT_ID");
        $req->execute();
        while ($result = $req->fetch()) {
            $u = new Type();
            $u->SetType($result['TDT_ID'], $result['TDT_NM']);
            array_push($list, $u);
        }
        $req->closeCursor();
        return $list;
    }

    /**
     * Get Database by ID
     * @param Integer $id
     * @return DB
     */
    public function GetById($id){
        $db = new DB();
        $req = $this->db->prepare("SELECT * FROM RFU_SGBD_DATABASE WHERE DB_ID = :id");
        $req->execute(array(
            ":id" => $id
        ));
        $res = $req->fetchAll();
        //print_r($res);
        $db->SetDB($res[0]['DB_ID'], $res[0]['DB_NNM'], $res[0]['DB_HOST'], $res[0]['DB_PORT'], $res[0]['DB_NM'], $res[0]['DB_TDT_ID'], $res[0]['DB_USER_ID'], $res[0]['DB_RULE_ID']);
        $req->closeCursor();
        return $db;
    }
    
}
